<?php

namespace app\api\controller;

use app\common\controller\Api;
use app\common\model\CashCode;
use think\Db;
use think\Request;
/**
 * 关键词搜索
 */
class Search extends Api
{

    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /**
     * 首页  http://fadmin.uni.com/api/search
     * 
     */
    public function index()
    {	
		
		
		$this->q_product('human','5');
		exit;
		$this->product_attr_color('red',5);
        $this->success('请求成功');
    }
	
	
	//前台搜索框动态匹配
	//$.post("http://api.uniwigs.com/api/search/search_ajax.html",{key:'aaaa'},function(res){console.log(res)});
	public function search_ajax(){
		
		if($this->request->isPost()  OR  $this->request->isGet() ){
			//Request::instance()->filter('htmlspecialchars');// 设置输入过滤 单个
			Request::instance()->filter(['strip_tags','htmlspecialchars','trim']);//多个strip_tags php html 标签及内容过滤htmlspecialchars 单双引号 特殊字符过滤
			
			/*uniwigs 原系统 提交的搜索 参数 
			 	q => '关键词'  ;
				limit => '搜索最大条数';
				timestamp => '时间戳';
				ajaxSearch => '是否为 ajax 搜索' ;
				id_lang=>'指定结果语言包'
			 */
			/* api 需要的提交参数 
				key => '关键词'  ;
				id_customer => '是否为登录用户搜索';
			*/
			
			$key = $this->request->post("key");//这里的输入个词汇过滤 在全局的config.php 文件中配置
			
			if($_REQUEST['q'] !=''){
				
				$key =$_REQUEST['q'];
			}
/* 			echo '<pre>';
			var_dump($key);
			echo '</pre>';
			die('debug!!!'); */
			
			
			//$key =trim($key);//过滤两边空格
			//$key =htmlspecialchars($key);//特殊字符  单双引号 过滤
			$item = 5;//控制最多结果条数

			//$key = 'human';
			
			
			//$result['hotkeyword'] = $this->q_hotkeyword($key);//热搜词匹配 如果存在 仅匹配一条精准匹配
			
			$result['hotkeyword'] = $this->q_product_result($key);//热搜词匹配 如果存在 仅匹配一条精准匹配
		
			$result['category'] = $this->q_category($key,$item);//分类匹配
			$result['cms'] = $this->q_cms($key,$item);//cms匹配
			$result['tag'] =$this->q_tag($key,$item);//tag 集合页面匹配
			$result['product'] = $this->q_product($key,$item);//产品匹配
			echo json_encode($result);
			
		}
		
		
	}
	
	
	//前台结果页面匹配
	//$.post("http://fadmin.uni.com/api/search/search.html",{key:'aaaa'},function(res){console.log(res)});
	public function search(){
		
		if($this->request->isPost()){
			
			//获取关键词
			$key = $this->request->post("key");
		
				
			return json_encode($key) ;
			
		}
		
		
	}
	
	
	//匹配热搜词  --增加多词匹配结果  匹配 则以第一个结果出现 则停止后面的匹配
	private function q_hotkeyword($keyword){
		
		$s_keys =  preg_split('#\s+#',$keyword);

		$search_keywords =[];
		$result = [];
		$i = 0 ;
		

		
		foreach($s_keys  as  $s_keyword){
			
			if(strlen($s_keyword) >2){
				
				//热搜词匹配 fa_keyword_hot   --如果不存在匹配结果 则进入正常流程匹配
				$sql = "select keywords,association from  fa_keyword_hot where keywords like '$s_keyword' and  status = '正常'";
			
				$hotres = Db::query($sql);

				if($hotres){
						$search_keywords[$i]['type'] = 'hot';
						$search_keywords[$i]['res'] = $hotres;

				}else{
					$search_keywords[$i] = $this->q_popular($s_keyword);
				
				
				}

				$i++;
				
			}
			
		}
		
		//判断搜索结果 第几个存在  则返回 
		$i = 1 ;
		$a = 0;
		foreach($search_keywords  as  $a ){
			
			if($a['type'] != 'empty'){
				
					$a = $i ;
					//已经搜索到结果  直接跳出循环
					break;
			}
			
			$i ++ ;
		}

		if($a > 0 ){
			
			$result = $search_keywords[$a-1];
			//返回结果 最多3个
			$result['res'] =	array_splice($result['res'],0,3);
			
		}else{
			$result['keyword']  = $s_keyword;
			$result['type'] = 'empty';
			$result['res']  = $s_keyword;
		}


		header('Content-Type:application/json');//指定返回类型为json
		

		//die(json_encode($result));
		return   json_encode($result);
		
		

	}

	//词库匹配结果   
	private  function  q_product_result($keyword){
		
		header('Content-Type:application/json');//指定返回类型为json
		//$keyword = ' front hair wigs  ' ;	
		//$keyword = ' hair' ;	

		//单个搜索词（全词匹配） 匹配--直接对客户搜索的框的词 进行全匹配
		
		$keyword = preg_replace('#\s+#',' ',trim($keyword));//过滤多余空格  去除两边空格
		
		$mul_keyword =  preg_split('#\s+#',trim($keyword));//拆分输入的词  去除多余空格  去除两边空格
	
		
		//先判断客户输入的是单个词  还是多个词
		if(count($mul_keyword) == 1){
			//单个词输入匹配
		/* 	echo  '单个输入词匹配'.'<br>';
			echo  '规则如下：  正向匹配 :比对产品词库 是否包含搜索词'.'<br>';
			echo  '规则如下：  反向匹配 :比对产品词库 是否被包含在搜索词内'.'<br>';
			echo  '规则如下：  容错匹配 :比对产品词库 是存在类似搜索词 并按相似度的高低进行排序'.'<br>';
			echo   '正向匹配结果 和反向匹配结果 是共存关系 --正向和反向均不存在结果的情况下 进行容错匹配'.'<br>';
			 */
			$res =  $this->q_popular($keyword);//获取匹配结果词 
			//结果不为空 则只取结果中的前3个
			if($res['type'] != 'empty'){
				
				$res['res'] =	array_splice($res['res'],0,3);//
			}
			
			//die( json_encode($res));	
			
			return  json_encode($res);
		}else{
			
	
		    //多个输入词匹配
		/* 	echo  '多个输入词匹配'.'<br>';
			echo   '先做全词匹配 看看是否存在结果';
			echo  '规则如下：分别单词匹配出 产品 以及搜索词结果 '.'<br>';
			echo  '规则如下：对比每个单词匹配结果 是否存在产品交集'.'<br>';
			echo  '规则如下：存在交集产品 则组合新词推荐给用户 或者存在匹配词交集 返回匹配词交集结果'.'<br>';
			echo   '以上都不存在结果 是否进行 存在即推荐词'.'<br>';
			echo  '<br>';
			echo  '<br>';
			echo  '<br>'; */
			//first -- 先做全词匹配
			$res =  $this->q_popular($keyword);//获取匹配结果词 

			
			//全词匹配 存在结果 直接返回结果
			if($res['type'] == 'empty'){
				//无结果  进行分词 匹配		分词 匹配 只要相似度 匹配度最高的一个词   如果每个词 都是多个则 匹配度不高
				$qres = [];
				$qvalue = [];
				$qproduct = [];
				$query_keyword =  '';
				$res_keyword = '';
				$i = 0 ;	
				
				foreach($mul_keyword  as $a){
					//echo '匹配词：'.$a .'<br>';
					
					if($this->q_popular($a)['type'] !='empty'){
						
						$qres[$i][$a] =$this->q_popular($a)['res'][0]; //获取匹配结果词 
						$qvalue[$i]   =$this->q_popular($a)['res'][0]; 
						$res_keyword .= $this->q_popular($a)['res'][0] .' '   ; 
						$query_keyword .=  "or keyword  like '".$this->q_popular($a)['res'][0]."'  ";
						$i++;	
						
					/* 	foreach( $this->q_popular($a)['res']  as  $b){
							
							$qres[$i][$a] = $b; //获取匹配结果词 
							$qvalue[$i] = $b; 

							$i++;	
							
						} */
						
					}

				}

				//如果 每个词 匹配出来的结果 不存在交集  则 进行产品结果匹配  
				if (count($qres) == count($qvalue)) {  
				    //echo '匹配搜索词 结果 不存在交集'; 
					//echo   ltrim( $query_keyword,'or');
					//直接去 匹配库里 是否存在 满足这几个关键词的 产品ID
					$qsql ="
						select mya.id_product , mya.num from  
						(select id_product,keyword ,COUNT(id_product) as  num   from  fa_keyword_pkeywords  where 
						( ". ltrim( $query_keyword,'or') .")and active = 1  GROUP BY  id_product  )mya   where  mya.num = " . count($mul_keyword);
						
					$q_product_res = Db::query ($qsql); 

					//存在结果 返回关联词
					if($q_product_res){
						
						$result = [];
						$result['keyword']  = $keyword;
						$result['type'] = 'similar';
						$result['res'][0]  = rtrim($res_keyword,' ');
						
						//测试结果输出
						//die( json_encode($result));
					
						return  json_encode($result);
						
					}else{
						$result = [];
						$result['keyword']  = $keyword;
						$result['type'] = 'empty';
						$result['res'][0]  = $keyword;
						
						//测试结果输出
						//die( json_encode($result));
						
						
						return  json_encode($result);
					}
					
					
				}else{
					//存在交集   则推送搜索词的匹配结果集 给客户  全词匹配 基本直接就会出结果了  所以这里不做处理了
					die('不做 匹配词交集处理');
					echo '<pre>';
					var_dump($qres);
					echo '</pre>';
					
				}

			}else{
				//echo  '全词匹配';
				//die( json_encode($res));
				return  json_encode($res);
					
			}

			
		}
	
	}
	

	
	
	
	//查询  搜索词 相关产品词库推荐词
	private  function   q_popular($keyword){
		$search_keywords=[];
		$search_keywords['keyword']= $keyword;
		//基础词库匹配
		$sql= 'SELECT	*	FROM	fa_keyword_pkeywords;';
		$getall= Db::query($sql);	
		$s_keyword  =$keyword;
		//正向匹配 存在结果  则推荐相关词上去  
		if(count($this->fiter_asc($s_keyword,$getall))){
			$search_keywords['type'] = 'asc';
			$search_keywords['res'] = $this->fiter_asc($s_keyword,$getall);
			
		}else{
			//反向匹配 存在结果  则推荐相关词上去  
			if(count($this->fiter_desc($s_keyword,$getall))){
				$search_keywords['type'] = 'desc';
				$search_keywords['res']  = $this->fiter_desc($s_keyword,$getall) ;
			}else{
				//容错匹配 存在结果  则推荐相关词上去  
				if(count($this->fiter_error($s_keyword,$getall))){
					
					$search_keywords['type'] = 'similar';
					$search_keywords['res']  = $this->fiter_error($s_keyword,$getall) ;
					
				}else{
					
					$search_keywords['type'] = 'empty';
					$search_keywords['res']  = $s_keyword;
					
				}

			}
		}
		
		
		//对搜索结果 增加相识度对比 并排序 
		if($keyword == 'front' and  $search_keywords['type'] != 'empty'){
			
			
			$similar  =new LCS();
			$myres =  $search_keywords['res'];
			$myattr = [];
			$new_myattr = [];
			$i = 0 ;
			foreach($myres as $a ){
				$myattr[$i]['keyword'] = $a;
				$myattr[$i]['percent'] = $similar->getSimilar($keyword ,$a);
				
				$i++;
			}
			
			array_multisort(array_column($myattr,'percent'),SORT_DESC,$myattr);//对匹配结果   进行相似度排序 
			$i= 0 ;
			foreach($myattr as $a){
				
				$new_myattr[$i] =  $a['keyword'];
				$i++;
			}
			$search_keywords['res']=$new_myattr;

		}

		return  $search_keywords;
		
	}
	
	
	//正向匹配词库  
	public function  fiter_asc($keyword,$res){
		
		$i=0;
		$asc_res =[];
		foreach($res  as $a){
			
				
				   
			//正向匹配 即 搜索词 包含于词库中的词 不区分大小写
			if(stripos($a['keyword'],$keyword ) !==false){
			
			 $asc_res[$i]=strtolower($a['keyword']);//全部转为 小写词汇 
			 $i++;
			}
			
		}
		
		$asc_res =   array_unique($asc_res);
		
		return  $asc_res;
	}
	
	
	//反向匹配词库  
	public function  fiter_desc($keyword,$res){
		
		$i=0;
		$dsc_res=[];
		foreach($res  as $a){
			//反向匹配即 词库中的词 包含于搜索词  不区分大小写 
			if(stripos($keyword ,$a['keyword']) !==false ){
				
					//匹配长度 不能小于3 
				if( strlen($a['keyword']) > 1){
					//匹配差长度 不能大于5
					if((strlen($keyword)  - strlen($a['keyword'])  ) <6 ){
						
						 $dsc_res[$i]=strtolower($a['keyword']);//全部转为 小写词汇 
						$i++;
						
					}
					
				}
			}
		}
		$dsc_res =   array_unique($dsc_res);//去重
		
		return  $dsc_res;
	}
	
	
	//容错匹配词库   最低容错度为 60%
	
	public  function fiter_error($keyword,$res){

		$similar  =new LCS();
		$i= 0;
		$similar_res = [];
		//相似度匹配 测试
		foreach($res  as $a){
			
			$pkeyword = strtolower($a['keyword']);
			//反向匹配即 词库中的词 包含于搜索词  不区分大小写 
			if(@$similar->getSimilar($keyword ,$pkeyword) >0.60 ){
				
				
				$similar_res[$i]['keyword']= $pkeyword;//全部转为 小写词汇 
				
				$similar_res[$i]['percent']= $similar->getSimilar($keyword,$pkeyword);//全部转为 小写词汇 
				$i++;
			}
		}
		
		array_multisort(array_column($similar_res,'percent'),SORT_DESC,$similar_res);//对相似度匹配结果 进行排序 
		$i = 0 ;
		foreach ($similar_res  as  $a ){
			
			$similar_res[$i] =  $a['keyword'];
			$i++;
		}

		
		$similar_res =   array_unique($similar_res);//结果去重 
	
		return  $similar_res;
	}
	
	
	
	
	
	
//-------------------------------------------------------------//
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	//包含匹配 category
	private function q_category($keyword,$item){
		
		//#link  返回关键词 连接  格式  40452-human-hair-lace-wigs    id_category-link_rewrite
		//模糊匹配 fa_keyword_hot
		$sql = "select id_category,name as keywords ,CONCAT(id_category,'-',link_rewrite) as link  
from  ps_category_lang  
where  `name` like  '%".$keyword."%' and id_lang =1 and id_shop=1   limit 0,$item ";
		
		$res = Db::query($sql);

		return json_encode($res) ;
	
	}
	//包含匹配 cms title 
	private function q_cms($keyword,$item){
		
	
		//模糊匹配 fa_keyword_hot
		$sql = "select id_cms,meta_title,meta_description,link_rewrite,CONCAT('content/',id_cms,'-',link_rewrite) as url from  ps_cms_lang  
where meta_title  like '%".$keyword."%'  and id_lang =1 and id_shop =1 
limit 0,5";
		
		$res = Db::query($sql);
		
		//如果此处无结果 则去搜寻是否存在 可以匹配内容的cms文章 如果有 则在这些文章里进行匹配
		if($res){
			
			return json_encode($res) ;
			
		}else{
			
			return $this->q_cms_filter($keyword,$item);
		}
		
	
	}
	
	
		
	//包含匹配 cms content 
	private function q_cms_filter($keyword,$item){
		
	
		//模糊匹配 fa_keyword_hot
	/* 	$sql = "select a.id_cms,c.id_cms_category,c.`name`,a.meta_title,a.content,CONCAT('content/',a.id_cms,'-',a.link_rewrite) as url  from  ps_cms_lang  a 
LEFT JOIN ps_cms b  on a.id_cms = b.id_cms 
LEFT JOIN ps_cms_category_lang  c  on  b.id_cms_category= c.id_cms_category and c.id_lang=1 and c.id_shop=1
where a.content  like '%".$keyword."%'   and a.id_lang =1 and a.id_shop =1 
limit 0,$item"; */
		// 增加后台 设置的 指定文章内容匹配 
		$sql ="select  a.id_cms ,b.meta_title,b.content,CONCAT('content/',b.id_cms,'-',b.link_rewrite) as url   from  fa_keyword_cms  a  
LEFT JOIN  ps_cms_lang  b  on a.id_cms = b.id_cms  and  b.id_lang =1 and b.id_shop =1 
where b.content  like '%".$keyword."%'  and a.`status` = '正常' limit 0,$item";
		
		$res = Db::query($sql);

		return json_encode($res) ;
	
	}
	
	
	//匹配 后期 博客 标题
	//内容 后期 需要指定 匹配
	private function  q_blog_content(){
		
		
		
	}
	

	
	//包含匹配 tag 
	private function q_tag($keyword,$item){
		
	
		//模糊匹配 
		$sql = "select a.id_tag,a.`name`, CONCAT('/tag/',REPLACE(a.`name`,' ','-')) as url from  ps_tag  a 
LEFT JOIN  px_tag_extra b  on a.id_tag = b.id_tag 
WHERE  id_lang = 1  and  a.`name` like '%".$keyword."%'
limit 0,$item";
		
		$res = Db::query($sql);

		return json_encode($res) ;
	
	}
	
	
	
	//匹配产品搜索词 --增加多词匹配结果  匹配
	private function q_keyword($keyword){
		
		//$keyword = 'haaaaaumxxxxan    hairxxxxx' ;
		$s_keys =  preg_split('#\s+#',$keyword);

		$search_keywords =[];
		$result = [];
		$i = 0 ;
		foreach($s_keys  as  $s_keyword){
			
			
			//热搜词匹配 fa_keyword_hot   --如果不存在匹配结果 则进入正常流程匹配
			$sql = "select keywords,association from  fa_keyword_hot where keywords like '$s_keyword' and  status = '正常'";
		
			$hotres = Db::query($sql);
			
			if(count($hotres)){
					$search_keywords[$i]['type'] = 'hot';
					$search_keywords[$i]['res'] = $hotres;

			}else{
				$search_keywords[$i] = $this->q_popular($s_keyword);

			}

			$i++;
		}
		
		 return  $search_keywords;
		
	}
	//匹配产品搜索词 --增加多词匹配结果  匹配
	private function q_search_keyword($keyword){
		
		//$keyword = 'haaaaaumxxxxan    hairxxxxx' ;
		$s_keys =  preg_split('#\s+#',$keyword);

		$search_keywords =[];
		$result = [];
		$i = 0 ;
		foreach($s_keys  as  $s_keyword){
			
			
			//热搜词匹配 fa_keyword_hot   --如果不存在匹配结果 则进入正常流程匹配
			$sql = "select keywords,association from  fa_keyword_hot where keywords like '$s_keyword' and  status = '正常'";
		
			$hotres = Db::query($sql);
			
			if(count($hotres) and false){
					$search_keywords[$i]['type'] = 'hot';
					$search_keywords[$i]['res'] = $hotres;

			}else{
				$search_keywords[$i] = $this->q_s_popular($s_keyword);

			}

			$i++;
		}
		
		 return  $search_keywords;
		
	}
	
	//搜索结果 --词库匹配  全部匹配 而非 依次向下匹配
	private function  q_s_popular($keyword){
		
		
		$search_keywords=[];
		$search_keywords['keyword']= $keyword;
		//基础词库匹配
		$sql= 'SELECT	*	FROM	fa_keyword_pkeywords;';
		$getall= Db::query($sql);	
		$s_keyword  =$keyword;
		$i=0;
		//正向匹配 存在结果  则推荐相关词上去  
		if(count($this->fiter_asc($s_keyword,$getall))){
			$search_keywords[$i]['type'] = 'asc';
			$search_keywords[$i]['res'] = $this->fiter_asc($s_keyword,$getall);
			$i++;
			
		}
		//反向匹配 存在结果  则推荐相关词上去  
		if(count($this->fiter_desc($s_keyword,$getall))){
				$search_keywords[$i]['type'] = 'desc';
				$search_keywords[$i]['res']  = $this->fiter_desc($s_keyword,$getall) ;
				$i++;
		}	

		return  $search_keywords;
	
	}
	
	
	//查询是否存在指定文章需要内容匹配【在匹配标题无结果的时候才执行】
	//规则1：搜索客户习惯购买产品 是否在结果内 在则排序最高
	//规则2：搜索结果中 进行产品销售 排序靠前
	//规则3：
	
	private function q_product($keyword,$item){
		$oldkeywors =  $keyword;
		
		//先判断搜索词是否存在  sku  reference  匹配结果 如果存在 则返回  sku 匹配结果  如果没有 则进入下一步
		
		$file_custom =    ' and  (b.name not like "%custom%")';
		$check_sku =  $sql = "SELECT
					a.id_product,
					a.reference,
					b.`name`,  
					a.id_category_default,
					CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url
					FROM
						 ps_product  a    
					LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and b.id_shop =1 
					LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
					WHERE  a.`visibility` IN ('both', 'search')    and     a.reference  like '%".$keyword."%'  and a.active = 1   ".$file_custom."  GROUP BY  a.id_product 
					 limit 0,$item
					" ;

		$check_sku = Db::query($check_sku);
		
		//存在 sku 匹配结果 优先返回此结果集 
		if(count($check_sku)){
			
			
			$this->filter($check_sku); //增加搜索结果过滤功能

			return  json_encode($check_sku);
		}

		$s_res = json_decode($this->q_product_result($keyword),true);
		
	
		
		
		if($s_res['type'] !='empty'){
			
			$keyword =  $s_res['res'];
			$like =  '';
			foreach($keyword as  $a){
				
				$like .= "or keyword like '%".$a."%'  ";
				
			
			}
			//产品名称 包含搜索词 
			
			//产品名称 不包含搜索词
			$is_filter =  "and ( b.name like '%wig%'  or b.name  like '%wigs%'  )"; //将结果中 产品名字 不包含搜索词的 过滤掉
			
			$sql = "SELECT
					a.id_product,
					d.reference,
					b.`name`,  
					d.id_category_default,
					CONCAT(c.link_rewrite,'/',d.id_product,'-',b.link_rewrite) as url
					FROM
						fa_keyword_pkeywords  a
					LEFT JOIN  ps_product d  on  a.id_product = d.id_product
					LEFT JOIN  ps_product_lang  b  on d.id_product = b.id_product and b.id_shop =1 
					LEFT JOIN  ps_category_lang  c  on  d.id_category_default = c.id_category and c.id_shop = 1
					WHERE  d.`visibility` IN ('both', 'search')    and  (". ltrim($like,'or').") and d.active = 1   ". $is_filter."  ". $file_custom ."  GROUP BY  a.id_product " ;
					
					

			//进行销量产品排序
			$sql .= " ORDER BY   field(a.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
			
			$res = Db::query($sql);

/* 			echo '<pre>';
			var_dump($sql);
			echo '</pre>';
			echo '<pre>';
			var_dump($res);
			echo '</pre>';
			die('debug!!!'. __LINE__); */
			$this->filter($res); 
			
			return  json_encode($res);
		
			
			
			
		}else{
			
			
			return  '[]';
		}

	
	
	}

	
	private  function filter(&$res){
		
		if(!count($res)){ return ;}
		foreach($res  as  $key =>$a){
			
			$category_limit =  Db::query('select filter from  fa_keyword_filter  where status ="正常" and  type =  "category"');  
			$product_limit =  Db::query('select filter from  fa_keyword_filter  where status ="正常" and  type =  "product"');  
			
			$del = false ;
			if($category_limit){
				
				$category_limit =  explode(',', $category_limit[0]['filter']);
				
				if(in_array($a['id_category_default'],$category_limit)){
					
						$del =  true;
				}
			}	
			
			if($product_limit){
				
				$product_limit =  explode(',', $product_limit[0]['filter']);
				
				if(in_array($a['reference'],$product_limit)){
					
						$del =  true;
				}
			}	
			
			if($del){
				
				unset($res[$key]);
			}


			
		}

	}
	
	//产品名称 匹配
	// 规则1 sku 无匹配结果后 执行 产品名称匹配
	private function  product_name($keyword,$oldkeywors,$item){
		
		header('Content-Type:application/json');//指定返回类型为json
		$id_customer = $this->request->post("id_customer");

		
		//先做sku 搜索  结果唯一  则终止结果显示
		$sql = "select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and  a.reference like '%".$oldkeywors."%' " ;
		
		$res = Db::query($sql);
		
		if(count($res) == 1){
			
			
			return  json_encode($res);
		}
		
		
		
		
		//再做普通搜索  结果唯一 则终止结果显示 
		//结果不唯一  或者为空 则进行多项模糊匹配搜索
		$sql =  "select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and    b.name like '%".$oldkeywors."%'  and b.name  not  like '%order%' ";

		$res = Db::query($sql);
		//全关键词匹配 结果唯一 则直接返回结果
		if(count($res) == 1){
			
			
			
			return  json_encode($res);
		}


		
		if(is_array($keyword)  and $keyword){
			
			$s=  '';
			foreach($keyword  as  $a){
			
			$s .=  "or   b.name like '%".$a."%' ";
			//echo  $a.'<br>';
			}
			
			
		//要排除掉自定义的 custom-products 分类下产品
		$query =  "   (".ltrim($s,'or').") ";
			
		}else{
			$keyword = $oldkeywors;
			//要排除掉自定义的 custom-products 分类下产品
		$query =  " b.name like '%".$keyword."%' ";
		}

		$sql =  "select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and   ". $query ." and b.name  not  like '%order%'";
		
	
		
		
		//如果是新客户 则执行销量排序   --老客户则执行购买习惯排序
		if($this->isOld(139469)){
			//老客户
			
			//先做 销量排序
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= " limit 0,$item" ;
				
				$res = Db::query($sql);

			
				
			}else{
				//存在用户购买 习惯排序
				if( $this->product_sort_habit(139469) == '' ){
					
					//不存在购买习惯 数据 默认销售数量排序
					$sql .= " ORDER BY   field(a.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
					$res = Db::query($sql);
		
					
					
				}else{
					
					//存在 则先对 销售数量排序 在此基础 再对购买习惯排序
					$sql = " select  * from  (
					select a.id_product,a.reference,b.`name`,  CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
where   a.active = 1 and     ". $query ."  and b.name  not  like '%order%'  ORDER BY   field(a.id_product,". $this->product_sort_sales().")   
desc   ) myaa 
ORDER BY	field(myaa.id_product,".$this->product_sort_habit(139469).") DESC

limit 0,5" ;
			
					$res = Db::query($sql);
	
					
				}
				
				
			}
			
			
		}else{
			//新客户 未注册用户
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= "  limit 0,$item" ;
				
				$res = Db::query($sql);

			
				
			}else{
				//存在产品 销售排行 id
				
				$sql .= "  ORDER BY   field(a.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
				$res = Db::query($sql);
	
			
				
			}
			
			
		}
		
		
		$res = $this->product_filter_out($res);//过滤结果
		
		return  json_encode($res);
		
	}
	

	
	//产品组合 feature 特征匹配
	//规则1 sku ，name 无匹配结果 才开始此处匹配
	private function  product_feature_color($keyword,$item){
		
		$id_customer = $this->request->post("id_customer");
		
		$sql = "select b.id_product,c.reference ,d.`name` ,
				CONCAT(f.link_rewrite,'/',d.id_product,'-',d.link_rewrite) as url

				from  ps_feature_value_lang  a 
				LEFT JOIN ps_feature_product  b  on  a.id_feature_value = b.id_feature_value
				LEFT JOIN ps_product  c  on  b.id_product = c.id_product and c.active =1
				LEFT JOIN ps_product_lang  d  on  c.id_product = d.id_product  and d.id_lang=1 and d.id_shop=1
				LEFT JOIN ps_category_lang  f  on c.id_category_default =f.id_category  and  f.id_shop=1
				where  a.`value`  like  '%".$keyword."%'  and  b.id_product  is not null  and c.reference  is not null
				 " ;
		
		if($this->isOld(139469)){
			
			if( $this->product_sort_habit(139469) == '' ){
					
					//不存在购买习惯 数据 默认销售数量排序
					$sql .= " ORDER BY   field(b.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
					$res = Db::query($sql);
		
				
					
					
			}else{
				
				//存在 则先对 销售数量排序 在此基础 再对购买习惯排序
					$sql = " select  * from  ( ".$sql."  ORDER BY   field(b.id_product,". $this->product_sort_sales().")   
desc   ) myaa 
ORDER BY	field(myaa.id_product,".$this->product_sort_habit(139469).") DESC

limit 0,$item " ;
					
					$res = Db::query($sql);

			
				
				
				
			}
			 
			
		}else{
			
			//新客户 未注册用户
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= "limit 0,$item";
				$res = Db::query($sql);

		
				
			}else{
				//存在产品 销售排行 id
				
				$sql .= "  ORDER BY   field(b.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
				$res = Db::query($sql);
	
				
				
			}
			
		}
		
		
		$res = $this->product_filter_out($res);//过滤结果
		return json_encode($res) ;
		
	}
	
	
	//产品组合 attr 组合颜色匹配
	//规则1 sku ，name ，feature 无匹配结果 才开始此处匹配
	private function  product_attr_color($keyword,$item){
		
		$id_customer = $this->request->post("id_customer");
		
		$sql = "select mya.*  from  (
select  a.`name`,c.id_product from  ps_attribute_lang  a 
LEFT JOIN  ps_product_attribute_combination   b  on a.id_attribute =b.id_attribute 
LEFT JOIN  ps_product_attribute  c  on b.id_product_attribute=c.id_product_attribute 
where  a.`name`  like  '%" .$keyword."%'  and  c.id_product  is not  null )mya  
LEFT JOIN  ps_product dd  on  mya.id_product = dd.id_product  
where  dd.active =1 
GROUP BY  mya.id_product
 " ;

		if($this->isOld(139469)){
			
			if( $this->product_sort_habit(139469) == '' ){
					
					//不存在购买习惯 数据 默认销售数量排序
					$sql .= " ORDER BY   field(mya.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
					$res = Db::query($sql);
		
				
					
					
			}else{
				
				//存在 则先对 销售数量排序 在此基础 再对购买习惯排序
					$sql = " select  * from  ( ".$sql."  ORDER BY   field(mya.id_product,". $this->product_sort_sales().")   
desc   ) myaa 
ORDER BY	field(myaa.id_product,".$this->product_sort_habit(139469).") DESC

limit 0,$item " ;	
		
					$res = Db::query($sql);
	

			}
			 
			
		}else{
			
			//新客户 未注册用户
			if($this->product_sort_sales() == ''){
				//不存在产品 销售排行 id
				$sql .= "limit 0,$item";
				$res = Db::query($sql);

				
			}else{
				//存在产品 销售排行 id
				
				$sql .= "  ORDER BY   field(mya.id_product,". $this->product_sort_sales().")   desc   limit 0,$item " ;
				
				$res = Db::query($sql);

				
			}
			
		}
		
		$res = $this->product_filter_out($res);//过滤结果
		
	    return json_encode($res) ;
	}
	
	
	// 在sku  name  attr  feature 均无结果 则进行相似匹配 推荐词	
	//相识度匹配 75%以上
	//人工指定 相似度匹配词汇	
	private function q_product_similar(){
		
		
		
	}
	
	
	//产品结果 筛选 根据客户购买产品的数量多少 做排序 新用户不做此排序
	//规则1. 先判断结果中产品 用户是否购买过 
	//规则2. 结果中存在购买结果 排序靠前
	//规则3. 结果中存在多个购买产品结果 再对购买产品数量做排序 靠前
	private function  product_sort_habit($id_customer){
		
		$sql= "select * from  (
select  a.id_order,a.id_customer,b.product_id,sum(b.product_quantity) as num  from  ps_orders a  
LEFT JOIN  ps_order_detail  b  on a.id_order=b.id_order
where  a.id_customer  = $id_customer  and b.product_id  is not null
GROUP BY  b.product_id  ) mya 
ORDER BY  mya.num  desc ";
		$res = Db::query($sql);
		
		$str =  '';
		foreach($res as $a){
			
			$str .= $a['product_id'] .',';
	
		}
		
		$str = rtrim($str,',');

		return  $str ;
	}
	//产品结果 筛选  对 用户购买习惯结果 做二次筛选 
	//规则1. 依据结果产品 的销量做排序
	//规则2. 销量时间--半年内的销量
	//规则3：如果统统为0 则不做排序
	//返回 产品 销售 排序 id
	private function  product_sort_sales(){
		
		$sql= 'select mya.product_id from  (
select  b.product_id,sum(b.product_quantity) as num  from  ps_orders a  
LEFT JOIN  ps_order_detail  b  on a.id_order=b.id_order
where b.product_id  is not null  and  a.date_add >= DATE_SUB(now(),INTERVAL 180 DAY)  
GROUP BY  b.product_id  ) mya 
ORDER BY  mya.num  desc 
#limit 0,100
';		
		$res = Db::query($sql);
		
		$str =  '';
		
		foreach($res as $a){
			
			$str .= $a['product_id'] .',';
	
		}
		
		$str = rtrim($str,',');

		return  $str ;
		
	}
	
	
	//判断是否 登陆  判断是否新老客户
	private function  isOld($id_customer){
		
		$sql = "select id_order from  ps_orders where  id_customer = $id_customer limit 0,1";
		$res = Db::query($sql);
		return $res; 
	}
	
	
	
	//结果中 过滤掉 禁用分类下的产品 
	private function  product_filter_out($res){
			
			$sql = "select GROUP_CONCAT(b.id_product) as productlist from  fa_keyword_category  a
LEFT JOIN  ps_category_product  b  on a.id_category= b.id_category

where  a.`status` = '正常'";

			$filter = Db::query($sql);
	
			if($filter[0]['productlist']){
				
				$i = 0 ;
				foreach($res  as $a){
					
					if( strpos('x'.$filter[0]['productlist'].',41770' , $a['id_product'].'' )){
						
						 unset($res[$i]);
						
					}
					$i++;	
				}
				
				return  $res ;
			
				
			}else{
				
				return $res ;
			}
	
		
	}
	
	

	
	
//------------------  搜索结果 处理页面 ---------------------------//	
	
	//搜索结果页面 
	public function  myresult(){
		
		
		
		if($this->request->isPost()){
			
			Request::instance()->filter(['strip_tags','htmlspecialchars','trim']);
			
			$keyword = $this->request->post("keyword");
		
			
			$category = $this->request->post("category");
			$sort = $this->request->post("sort");
			$page = $this->request->post("page");
			$item = $this->request->post("item");

			$this->sort_new($keyword,$sort,$category,$page,$item); 
			
			
		}
		
	}

	
	//结果排序
	private function  sort_new($keyword,$sort,$category,$page,$item){
		
		//获取查询
		$limit  =  ($page == '1')? " limit 0," . $item*1 : " limit ". ($page-1) *$item  ."," . $item*1;
		
		//最新排序  低到高
		if($sort == 'new_down' ){
		  $orderby  =  ' order  by  id_product  desc ';
		}
		//最新排序  高到低
		if($sort == 'new_up' ){
		  $orderby  =  ' order  by  id_product  asc ';
		}
		
		//产品价格排序 高到低
		if($sort ==  'price_down'){
			 $orderby  =  ' order  by   a.price  desc ';
			
		}
		
		//产品价格排序 低到高
		if($sort ==  'price_up'){
			
			 $orderby  =  ' order  by   a.price  asc ';
		}
		
		//产品销量排序  高到低
		if($sort ==  'sales_down'){
			
			 $orderby = "  ORDER BY   field(a.id_product,". $this->product_sort_sales().")   desc  " ;
		}
		//产品销量排序  低到高
		if($sort ==  'sales_up'){
			$orderby = "  ORDER BY   field(a.id_product,". $this->product_sort_sales().")   asc  " ;
			
		}
		
		//$keyword =  "front";
		$s_res = json_decode($this->q_product_result($keyword),true);//匹配词库搜索词 
/* 		echo '<pre>';
		var_dump($s_res);
		echo '</pre>'; */
		//如果搜索词 未匹配词库  则直接返回 空 
		if($s_res['type'] == 'empty'){
			
			
			return  '[]';
		}

		//匹配结果词 有多个  
		$s_where = '';
		if(count($s_res['res']) > 1){
		
			foreach($s_res['res'] as $a){
				
				$s_where .= "  keyword like '%".$a ."%' or" ;
			}
		}else{
			
			$s_where .= "keyword like '%". $s_res['res'][0]."%'" ;
			
		}
		$s_where = rtrim($s_where,'or');

		//获取匹配词 管理的产品 id 
		$plistsql = "(SELECT
					id_product as pid 
					FROM
						fa_keyword_pkeywords  
					WHERE( ".$s_where.")and active = 1  
GROUP BY  id_product) mya ";
		
		
		
		//是否 指定某分类下进行搜索
		$category = ($category == '0')?"": " and  id_category =$category ";
		
		$sql =  "select a.id_product,a.reference,b.`name`, d.id_image  ,
		b.link_rewrite ,a.price, CONCAT(c.link_rewrite,'/',a.id_product,'-',b.link_rewrite) as url,
		mya.pid 
		from  
		ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
LEFT JOIN  ps_image  d  on b.id_product = d.id_product  and  d.cover =1
LEFT JOIN  ps_product_shop  f  on a.id_product  = f.id_product and f.id_shop = 1
left  join  $plistsql  on  mya.pid = a.id_product 
where   a.active = 1  and  f.id_product  is not null  and mya.pid  is not null   
 and b.name not like  '%Custom Order%'    $category    $orderby    $limit ";
	

		$categorylist = "select  c.id_category  ,c.`name`  from  ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
left  join  $plistsql  on  mya.pid = a.id_product 
where   a.active = 1 and  mya.pid  is not null   and  c.id_category is not null    GROUP BY  c.id_category"; 


		$page ="select a.id_product
		from  
		ps_product a 
LEFT JOIN  ps_product_lang  b  on a.id_product = b.id_product and id_shop =1 
LEFT JOIN  ps_category_lang  c  on  a.id_category_default = c.id_category and c.id_shop = 1
LEFT JOIN  ps_image  d  on b.id_product = d.id_product  and  d.cover =1
LEFT JOIN  ps_product_shop  f  on a.id_product  = f.id_product and f.id_shop = 1
left  join  $plistsql  on  mya.pid = a.id_product 
where   a.active = 1  and  f.id_product  is not null  and mya.pid  is not null   
 and b.name not like  '%Custom Order%'    $category    $orderby  "  ;
		

		$pagenum = count(Db::query($page));

		if($pagenum  <=$item){
			
			$pagenum =0;
			
		}else{
			
			$pagenum  = (int)($pagenum/$item)  + 1;
			
		}
		

		
		$res =  Db::query($sql);
		
		
		$i = 0;
		foreach($res  as  $a){
			//$res[0]['oldprice'] .  '--' .$res[0]['realprice']
			$res[$i]['oldprice']  =	$this->get_product_price($a['id_product'])[0]['oldprice'];
			$res[$i]['realprice']  =	$this->get_product_price($a['id_product'])[0]['realprice'];
			
			if(isset($this->get_product_price($a['id_product'])[0]['id_product'])){
				
				$res[$i]['attrimg']  = '';
			}else{
				
				$res[$i]['attrimg']  =  $this->get_product_attrimg($a['id_product']);
				
			}
			
			$i++;
		}

		
		$categorylist = Db::query($categorylist);
		
		$res["categorylist"] = $categorylist;
		$res["pagenum"] = $pagenum;
		
	
		
		
		echo   json_encode($res);
		exit;
		
		
	}
	//获取产品价格 
	private  function  get_product_price($id_product){
		
		//判断此产品 是否存在组合 
		$checkattr =  Db::query('select  id_product_attribute from  ps_product_attribute  where  id_product =  '.$id_product .' limit 0,1');
		
		if($checkattr){
			
			$sql="select 
a.price,
b.price +a.price as oldprice ,
IF(c.reduction is null ,( b.price +a.price ), ( b.price +a.price )-  c.reduction)    as  realprice 
 from  ps_product  a  
LEFT JOIN  ps_product_attribute  b  on a.id_product  =  b.id_product
LEFT JOIN  ps_specific_price  c on  a.id_product = c.id_product
where 
a.id_product = $id_product  and b.default_on = 1";
			$res = Db::query($sql);
			if(!$res){
				$sql="select 
a.price,
b.price +a.price as oldprice ,
IF(c.reduction is null ,( b.price +a.price ), ( b.price +a.price )-  c.reduction)    as  realprice 
 from  ps_product  a  
LEFT JOIN  ps_product_attribute  b  on a.id_product  =  b.id_product
LEFT JOIN  ps_specific_price  c on  a.id_product = c.id_product
where 
a.id_product = $id_product limit 0,1";
				
				$res = Db::query($sql);
			}
			
			return  $res;
			
		}else{
			$sql="select 
a.id_product,a.price as oldprice ,
IF(c.reduction is null,a.price,a.price - c.reduction )as realprice
from  ps_product  a  
LEFT JOIN  ps_specific_price  c on  a.id_product = c.id_product
where 
a.id_product = $id_product  ";
			
			$res = Db::query($sql);
			return  $res;
		}
		
	}
	
	//获取产品组合img 
	public  function  get_product_attrimg($id_product){
		
		if($_SERVER['HTTP_HOST'] == 'www.uni.com'){
			
			$host =  'http://www.uni.com';
		}else{
			$host =  'https://www.uniwigs.com';
			
		}
		
		$sql ="select c.id_attribute,CONCAT('".$host."/img/co/',c.id_attribute,'.jpg') as imgurl from  ps_product_attribute a   
LEFT JOIN  ps_product_attribute_combination  b  on a.id_product_attribute = b.id_product_attribute 
LEFT JOIN  ps_attribute  c  on  b.id_attribute = c.id_attribute  
where  a.id_product  =  $id_product  and  c.id_attribute_group  =26   and  a.available_date ='0000-00-00'
GROUP BY  c.id_attribute";
		$res = Db::query($sql);
		return  $res;
	}
	
	
	
}


class LCS {
  var $str1;
  var $str2;
  var $c = array();
  /*返回串一和串二的最长公共子序列
*/
  function getLCS($str1, $str2, $len1 = 0, $len2 = 0) {
    $this->str1 = $str1;
    $this->str2 = $str2;
    if ($len1 == 0) $len1 = strlen($str1);
    if ($len2 == 0) $len2 = strlen($str2);
    $this->initC($len1, $len2);
    return $this->printLCS($this->c, $len1 - 1, $len2 - 1);
  }
  /*返回两个串的相似度
*/
  function getSimilar($str1, $str2) {
    $len1 = strlen($str1);
    $len2 = strlen($str2);
    $len = strlen($this->getLCS($str1, $str2, $len1, $len2));
    return $len * 2 / ($len1 + $len2);
  }
  function initC($len1, $len2) {
    for ($i = 0; $i < $len1; $i++) $this->c[$i][0] = 0;
    for ($j = 0; $j < $len2; $j++) $this->c[0][$j] = 0;
    for ($i = 1; $i < $len1; $i++) {
      for ($j = 1; $j < $len2; $j++) {
        if ($this->str1[$i] == $this->str2[$j]) {
          $this->c[$i][$j] = $this->c[$i - 1][$j - 1] + 1;
        } else if ($this->c[$i - 1][$j] >= $this->c[$i][$j - 1]) {
          $this->c[$i][$j] = $this->c[$i - 1][$j];
        } else {
          $this->c[$i][$j] = $this->c[$i][$j - 1];
        }
      }
    }
  }
  function printLCS($c, $i, $j) {
    if ($i == 0 || $j == 0) {
      if ($this->str1[$i] == $this->str2[$j]) return $this->str2[$j];
      else return "";
    }
    if ($this->str1[$i] == $this->str2[$j]) {
      return $this->printLCS($this->c, $i - 1, $j - 1).$this->str2[$j];
    } else if ($this->c[$i - 1][$j] >= $this->c[$i][$j - 1]) {
      return $this->printLCS($this->c, $i - 1, $j);
    } else {
      return $this->printLCS($this->c, $i, $j - 1);
    }
  }
} 

